/**************************************************************************/
/**************************************************************************/
USE MASTER
GO
IF EXISTS (SELECT * FROM SYSDATABASES where name = 'Bank')
BEGIN
	DROP DATABASE Bank;
END 
GO
CREATE DATABASE [Bank] 
GO
USE [Bank]
GO


/***************************************************************************/
			/*	  Table [dbo].[User]    */
/***************************************************************************/
CREATE TABLE [dbo].[User]
(
	[userID] NVARCHAR(5) NOT NULL PRIMARY KEY,
	[password] NVARCHAR(30)  NULL,
	[fullName] NVARCHAR(30) NOT NULL,
	[address] NVARCHAR(30) NULL,
	[email] NVARCHAR(30) NOT NULL,
	[telephone] NVARCHAR(30) NULL,
	[gender] NVARCHAR(30) NULL,
)

GO

/***************************************************************************/
			/*     Table [dbo].[AccountType]    */
/***************************************************************************/
CREATE TABLE [dbo].[AccountType]
(
	[typeID] NVARCHAR(5) NOT NULL PRIMARY KEY,
	[typeName] NVARCHAR(30) NOT NULL,	
)
GO

/***************************************************************************/
				/*	  Table [dbo].[Account]    */
/***************************************************************************/
/*DROP TABLE Products*/
CREATE TABLE [dbo].[Account]
(
	[accountID] NVARCHAR(5) NOT NULL PRIMARY KEY,
	[userID] NVARCHAR(5) NOT NULL FOREIGN KEY REFERENCES [User] (userID),
	[typeID] NVARCHAR(5) NOT NULL FOREIGN KEY REFERENCES AccountType (typeID),
	[balance] INT NOT NULL,
	[currencyCode] NVARCHAR(5) NOT NULL,
	[withdrawable] BIT NOT NULL,
	[depositable] BIT NOT NULL
)

GO

/***************************************************************************/
			/*	  Table [dbo].[Currency]    */
/***************************************************************************/
CREATE TABLE [dbo].[Currency]
(
	[currencyCode] NVARCHAR(5) NOT NULL PRIMARY KEY,
	[currencySymbol] NVARCHAR(30) NOT NULL,	
)
GO

/***************************************************************************/
			/*	  Table [dbo].[Transaction]    */
/***************************************************************************/
CREATE TABLE [dbo].[Transaction]
(
	[transactionID] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	[accountID] NVARCHAR(5) NOT NULL  FOREIGN KEY REFERENCES Account (accountID),	
	[date] DATETIME NOT  NULL,
	[action] NVARCHAR(200)  NULL
)
GO